﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace BuildForm
{
    public partial class OrderDetails : Form
    {
        string orderid;
        bool isNew;
        SqlConnection con = new SqlConnection();

        #region MyCode
        public OrderDetails(string orderid, bool isNew)
        {
            try
            {
                this.orderid = orderid;
                this.isNew = isNew;
                InitializeComponent();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                txtOrderID.Text = orderid;
                cbSearchBy.SelectedIndex = 0;
                LoadOrderDetailsInfo();
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
                btnReload.Enabled = false;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }

        void LoadOrderDetailsInfo()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("SELECT * FROM Sales.OrderDetails WHERE orderid = '{0}'", orderid);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    gvOrderDetails.Rows.Add(rd[0].ToString(), rd[1].ToString(), rd[2].ToString(), rd[3].ToString(), rd[4].ToString());
                }
                rd.Close();
            }
            catch (Exception)
            {

                MessageBox.Show("Can not load!");
            }
            con.Close();

        }

        void AddOrderDetails()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_OrderDetails_Insert";

                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = orderid;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = cbProductID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@unitprice", SqlDbType.Money);
                param.Value = nudUnitPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@qty", SqlDbType.SmallInt);
                param.Value = txtQty.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@discount", SqlDbType.Float);
                param.Value = nudDiscount.Text.Trim();
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Add successful!");
            }
            catch (Exception ex)
            {

                throw (ex);
            }
            con.Close();

        }

        void UpdateOrderDetails()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_OrderDetails_Update";

                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = orderid;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = cbProductID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@unitprice", SqlDbType.Money);
                param.Value = nudUnitPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@qty", SqlDbType.SmallInt);
                param.Value = txtQty.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@discount", SqlDbType.Float);
                param.Value = nudDiscount.Text.Trim();
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Update successful!");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            con.Close();
        }

        void DeleteOrderDetails()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_OrderDetails_Delete";

                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = txtOrderID.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = cbProductID.SelectedValue;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Delete successful!");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            con.Close();
        }

        void getDataSource_Products()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT productid, productname FROM Production.Products";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "Production.Products");
            DataTable dt = ds.Tables["Production.Products"];
            cbProductID.DataSource = dt;
            cbProductID.ValueMember = "productid";
            cbProductID.DisplayMember = "productname";
        }

        void SearchOrderDetails()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                if (cbSearchBy.SelectedIndex == 0)
                {
                    cmd.CommandText = string.Format("SELECT a.* FROM Sales.OrderDetails a,(SELECT productid FROM Production.Products "
                    + "WHERE productname LIKE '%{0}%') b WHERE a.productid = b.productid AND a.orderid = '{1}'", txtSearch.Text.Trim(), txtOrderID.Text);
                }
                else
                {
                    if (cbSearchBy.SelectedIndex == 1)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM Sales.OrderDetails WHERE unitprice = '{0}' AND orderid = '{1}'", txtSearch.Text.Trim(), txtOrderID.Text);
                    }
                }
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    gvOrderDetails.Rows.Add(rd[0].ToString(), rd[1].ToString(), rd[2].ToString(), rd[3].ToString(), rd[4].ToString());
                }
                rd.Close();
            }
            catch (Exception)
            {

                MessageBox.Show("Can not load search data!");
            }
            con.Close();
        }

        void initData()
        {
            errorProvider1.Clear();
            gvOrderDetails.ClearSelection();
            txtOrderID.Text = orderid;
            cbProductID.SelectedIndex = 0;
            txtQty.Text = "";
            nudUnitPrice.Text = "0.0000";
            //txtSearch.Text = "";
            nudDiscount.Text = "0.000";
           

            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            btnAdd.Enabled = true;
            btnNew.Enabled = true;
        }

        bool ValidateOrderDetails()
        {
            errorProvider1.Clear();
            bool error = false;
            if (nudUnitPrice.Text.Trim().Length > 0)
            {
                decimal tmp;
                try
                {
                    tmp = Decimal.Parse(nudUnitPrice.Text.Trim());
                    if (tmp < 0)
                    {
                        errorProvider1.SetError(nudUnitPrice, "Invalid Input!");
                        error = true;
                    }
                }
                catch (Exception)
                {
                    errorProvider1.SetError(nudUnitPrice, "Invalid Input!");
                    error = true;
                }
                
            }
            else
            {
                nudUnitPrice.Text = "0.0000";
            }

            if (txtQty.Text.Trim().Length > 0)
            {
                try
                {
                    short tmp = short.Parse(txtQty.Text);
                    if (tmp < 0)
                    {
                        errorProvider1.SetError(txtQty, "Invalid Input!");
                        error = true;
                    }
                }
                catch (Exception)
                {
                    errorProvider1.SetError(txtQty, "Invalid Input!");
                    error = true;
                }
            }
            else
            {
                txtQty.Text = "1";
            }

            if (nudDiscount.Text.Trim().Length > 0)
            {
                try
                {
                    double tmp = Double.Parse(nudDiscount.Text.Trim());
                    if (tmp > 1 || tmp < 0)
                    {
                        errorProvider1.SetError(nudDiscount, "Out of Range!");
                        error = true;
                    }
                }
                catch (Exception)
                {
                    errorProvider1.SetError(nudDiscount, "Invalid Input!");
                    error = true;
                }
            }
            else
            {
                nudDiscount.Text = "0.000";
            }

            if (error == true)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region Event
        private void OrderDetails_Load(object sender, EventArgs e)
        {
            getDataSource_Products();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (ValidateOrderDetails() == true)
                {
                    errorProvider1.Clear();
                    AddOrderDetails();
                    gvOrderDetails.Rows.Clear();
                    LoadOrderDetailsInfo();
                }
            }
            catch (Exception)
            {

                MessageBox.Show("Can not Add!");
            }
        }

        private void gvOrderDetails_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                if (gvOrderDetails.SelectedRows.Count > 0)
                {
                    errorProvider1.Clear();
                    DataGridViewRow r = gvOrderDetails.SelectedRows[0];
                    cbProductID.SelectedValue = r.Cells[1].Value.ToString().Trim();
                    txtQty.Text = r.Cells[3].Value.ToString().Trim();
                    nudUnitPrice.Text = r.Cells[2].Value.ToString().Trim();
                    nudDiscount.Text = r.Cells[4].Value.ToString().Trim();

                    btnDelete.Enabled = true;
                    btnUpdate.Enabled = true;

                    btnAdd.Enabled = false;
                }
            }
            catch (Exception)
            {

                MessageBox.Show("Get Value Fail !", "Error");
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (gvOrderDetails.SelectedRows.Count > 0)
                {
                    DialogResult r = MessageBox.Show("Are you Sure?", "Confirm?", MessageBoxButtons.YesNo);
                    if (r == DialogResult.Yes)
                    {
                        DeleteOrderDetails();
                        gvOrderDetails.Rows.Clear();
                        LoadOrderDetailsInfo();
                        initData();
                    }
                }
            }
            catch (Exception)
            {

                MessageBox.Show("Can not delete!");
            }

        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                if (gvOrderDetails.SelectedRows.Count > 0)
                {
                    if (ValidateOrderDetails() == true)
                    {

                        DialogResult r = MessageBox.Show("Are you Sure?", "Confirm?", MessageBoxButtons.YesNo);
                        if (r == DialogResult.Yes)
                        {
                            UpdateOrderDetails();
                            gvOrderDetails.Rows.Clear();
                            LoadOrderDetailsInfo();
                        }
                    }
                }

            }
            catch (Exception)
            {

                MessageBox.Show("Can not Update");
            }

        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtSearch.Text != "")
                {

                    gvOrderDetails.Rows.Clear();
                    SearchOrderDetails();
                    initData();
                    //btnNew.Enabled = false;
                    //btnAdd.Enabled = false;
                    btnReload.Enabled = true;
                }
                else
                {
                    MessageBox.Show("Please Input value to search!");
                }
            }
            catch (Exception)
            {

                MessageBox.Show("Can not search!");
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            initData();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            try
            {
                gvOrderDetails.Rows.Clear();
                LoadOrderDetailsInfo();
                initData();
                txtSearch.Text = "";
                btnReload.Enabled = false;
                cbSearchBy.SelectedIndex = 0;
            }
            catch (Exception)
            {

                MessageBox.Show("Fail Connect to Database", "Error");
            }
        }
        #endregion

        private void OrderDetails_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (isNew == true)
            {
                MessageBox.Show("Add Order successful!");
            }
        }


    }
}
